"""
Redistribution and use in source and binary forms, with or
without modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice, this
list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer
in the documentation and/or other materials provided with the distribution.
* Neither the names of the authers, nor the names of its contributors, may be used to endorse or 
promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE REGENTS/COPYRIGHT HOLDERS AND CONTRIBUTORS ``AS IS'' AND 
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE 
REGENTS/COPYRIGHT OWNERS AND CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER 
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF 
ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

@Author Fredrique Samuels <fredriquesamuels@gmail.com>
@database.py  
"""

import MySQLdb
import time


class MySqlConnectionData:
     def __init__(self):
        self.conn = None
        self.cur = None

     def execute(self,sql):
         self.cur.execute(sql)
         
     def get(self,sql):
         self.cur.execute(sql)
         return self.cur.fetchall()

#========================================================#

class MySQLDB:
    """ Class for manipulating data bases data """
    def __init__(self,host='127.0.0.1',user ='root',passw=''):
        """Class Contructor

        @param host: DB host
        @param user: user
        @param passw: password
        """
        self._host = host
        self._user = user
        self._passw = passw
        self._connection = MySqlConnectionData()
        
    def setUser(self,user):
        """ Set the user name"""
        self_user = user

    def getUser(self):
        """ Get user """
        return self._user

    def setHost(self,host):
        """ Set the DB host ip"""
        self._host = host

    def getHost(self):
        """ Get host ip"""
        return self_host

    def setPassword(self,passw):
        """ Set user password """
        self._paw = passw

    def getPassword(self):
        """ Get the user password"""
        return self._passw

    def connect(self,db_name):
        """ Connect to db """
        try:
            self._connection.conn = MySQLdb.connect (host = self._host,
                                     user = self._user,
                                     passwd = self._passw,
                                     db = db_name)
            self._connection.cur = self._connection.conn.cursor ()
        except Exception,e:
           print e
           return False       
        return True

    def disconnect(self):
        """
        Commits any cahnges to database and disconnects on exit.
        If unable to do so it retuns False and returns True on success.
        """
        if self._connection.cur is None or self._connection.conn is None:
             return True
        try:
            self._connection.cur.close()
            self._connection.conn.commit()
            self._connection.conn.close()
            self._connection.conn = None
            self._connection.cur = None            
        except Exception,e:
            return False
        return True

    def __del__(self):
         self.disconnect()
         
    def table_exists(self,table):
        """
        Uses self.cur to execute a MSQL query to fetch all table names in
        specified data base. Then checks whether the table name given 
        is in the list of tables. 
        Function may throw an exception if self.cur is None.
        """
        tables = []

        temp = self._connection.get('SHOW TABLES')           
        for i in temp:
            tables.append(i[0])  
        return table in tables

    def create_table(self,table,**prop):
        """ Create a table with the given properties
        e.g create_table('table1',
                         id='INTEGER AUTO_INCREMENT',
                         name = 'CHAR(10)',
                         age = 'INTEGER(5)',
                         unique_props = [name],
                         primary_keys = 'id'
                         )

        This will produces a query a follows

        CREATE TABLE table1 (id INTEGER AUTO_INCREMENT, PRIMARY KEY (id),
            PRIMARY KEY (id),name CHAR(30),UNIQUE (name),age INTEGER(5))
            """
       
        unique_props = []
        primary_keys = []

        if 'primary_key' in prop.keys():
            primary_keys = prop['primary_key']

        if 'unique' in prop.keys():
            unique_prop = prop['unique']

        sql = "CREATE TABLE "
        sql += table
        sql += "("
        p_n = 0
        for p in prop.keys():
            if not ( p.upper() in ['PRIMARY_KEY','UNIQUE'] ):
                if p_n > 0:
                    sql +=','
                sql += p +' '+ prop[p]
                if p in primary_keys:
                    sql+=",PRIMARY KEY ("+p+")"
                if p in unique_props:
                    sql+=",UNIQUE ("+p+"),"
                p_n+=1
        sql+=')'
        self._connection.execute(sql)

    def insert(self,table,**prop):
        sql = 'INSERT INTO '+table+'('

        c = 0
        for p in prop.keys():
            if c>0:
                sql+=','
            sql+=p
            c+=1
        sql+=') VALUES ('
        c = 0
        for p in prop.keys():
            if c>0:
                sql+=','
            sql+="\'"+str(prop[p])+"\'"
            c+=1
        sql+=')'
        self._connection.execute(sql)

    def get(self,table,props = [],condition={},where=[],order = None,limit=None):
          """ Get filtered data"""
          sql="SELECT "
          c=0
          for p in props:
               if c>0:
                    sql+=','
               sql+=str(p)
               c+=1
          sql+=" FROM "+table

          if len(where)>0:
               sql+=' WHERE '
               c = 0
               for w in where:
                    if c>0:
                         sql+=','
                    sql+= str(w[0])+'=\''+str(w[1])+"\'"
                    c+=1

          if not order is None:
               sql+=' ORDER BY '+str(order)

          if not limit is None:
               sql+=' LIMIT '+str(int(limit))

          return self._connection.get(sql)

    def update(self,table,**prop):
          sql = 'Update '+table+' SET '
          c = 0
          for p in prop.keys():
               if not (p.upper() in ['WHERE']):
                    if c>0:
                          sql+=','
                    sql+= p+'='+str(prop[p])
                    c+=1
          if 'where' in prop.keys():
               _where = prop['where']
               print _where
               if len(_where)>0:
                    sql+=' WHERE '
                    c = 0
                    for w in _where:
                         if c>0:
                              sql+=','
                         sql+= str(w[0])+'=\''+str(w[1])+"\'"
                         c+=1
          self._connection.execute(sql)
          
          

if __name__ == '__main__':
    
    mysql = MySQLDB()
    mysql.connect('my_database')
    table = 'table_log'
  
    if not mysql.table_exists(table):
        mysql.create_table(table,
                         id='INTEGER AUTO_INCREMENT',
                         name = 'CHAR(10)',
                         age = 'INTEGER(5)',
                         unique = ['name'],
                         primary_key = 'id'
                         )
##    mysql.update(table,age='100',where=[('id','max(id)')])
    print mysql.get(table,['max(id)','name','age'],limit=1)
    mysql.disconnect()
    
    
##    data = [('mike',90),
##            ('coby',100),
##            ('jazz',9),
##            ('kyle',50)]
##    for d in data:
##        mysql.insert(table,name=d[0],age=d[1])
      
##    del mysql
   
            
